<?php
require("menuBarHead.php");
require("db_manage/dbFunctions.php");

$dbh = connectDB();
$query = "select rd_id,qr_code,requestor_id,projected_checkin,has_been_checked_in from Resource_Designation where has_been_checked_in = 0 AND projected_checkin < NOW()";
$stmt = $dbh->prepare($query);
$stmt->execute();

$qr_codes_checked_out = $stmt->fetchAll(PDO::FETCH_ASSOC);

$equipment;
$rooms;
foreach( $qr_codes_checked_out as $qr )
{
	$equip_query = "SELECT * FROM Equipment WHERE qr_code=:qr";
	$rooms_query = "SELECT * FROM Room WHERE qr_code=:qr";
	$req_details_query = "SELECT * FROM Requestors WHERE req_id=:rid";

	$stmt = $dbh->prepare($req_details_query);
	$stmt->bindParam(':rid',$qr['requestor_id']);
	$stmt->execute();
	$req_details = $stmt->fetch(PDO::FETCH_ASSOC);

	$req_name = $req_details['first_name'] . " " . $req_details['last_name'];
	$req_email = $req_details['email'];

	$stmt = $dbh->prepare($equip_query);
	$stmt->bindParam(':qr',$qr['qr_code']);
	$stmt->execute();
	if( $stmt->rowCount() == 1 )
		$equipment[] = array($stmt->fetch(PDO::FETCH_ASSOC),$qr['projected_checkin'],$req_name,$req_email);

	$stmt = $dbh->prepare($rooms_query);
	$stmt->bindParam(':qr',$qr['qr_code']);
	$stmt->execute();
	if( $stmt->rowCount() == 1 )
		$rooms[] = array($stmt->fetch(PDO::FETCH_ASSOC),$qr['projected_checkin'],$req_name,$req_email);
}

//start div
print("<div id=\"overdue-equipment\">");

//PRINT EQUIPMENT CHECKED OUT TABLE 
print("<h2>Overdue Equipment Currently Checked Out</h2>");
print("<table border=1>".
		"<tr><th>QR Code</th>".
			"<th>Equipment Name</th>".
			"<th>Serial Number</th>".
			"<th>Current Condition</th>".
			"<th>Projected Checkin</th>".
			"<th>Requestor With Equipment</th>".
			"<th>Requestor Email</th></tr>");
foreach( $equipment as $e )
{
	print("<tr><td>".$e[0]['qr_code']."</td>".
			"<td>".$e[0]['equipment_name']."</td>".
			"<td>".$e[0]['serial_number']."</td>".
			"<td>".$e[0]['current_condition']."</td>".
			"<td>".$e[1]."</td>".
			"<td>".$e[2]."</td>".
			"<td>".$e[3]."</td></tr>\n");
}
print("</table>");

//PRINT ROOMS CHECKED OUT TABLE
print("<h2>Overdue Rooms Currently Checked Out</h2>");
print("<table border=1>".
	"<tr><th>QR Code</th>".
	"<th>Building</th>".
	"<th>Floor</th>".
	"<th>Room Number</th>".
	"<th>Room Type</th>".
	"<th>Projected Checkin</th>".
	"<th>Requestor With Room</th>".
	"<th>Requestor Email</th></tr>");
foreach( $rooms as $r )
{
	print("<tr><td>".$r[0]['qr_code']."</td>".
			"<td>".$r[0]['building']."</td>".
			"<td>".$r[0]['floor']."</td>".
			"<td>".$r[0]['room_number']."</td>".
			"<td>".$r[0]['room_type']."</td>".
			"<td>".$r[1]."</td>".
			"<td>".$r[2]."</td>".
			"<td>".$r[3]."</td>\n");
}
print("</table>");
print("</div>");//end div

require("menuBarClose.php");
?>
